﻿package com.wk.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.concurrent.LinkedBlockingQueue;

import com.wk.database.entity.Update;
import com.wk.job.Task;

public class DB_Mysql {

	private static Connection conn = null;// 数据库连接

	public static Connection getConnection() {
		if (conn == null) {
			initConnection();
		}

		return conn;
	}

	public DB_Mysql() {

	}

	/***
	 * 初始化连接
	 * 
	 * @return
	 */
	private static boolean initConnection() {

		try {
			Class.forName("com.mysql.jdbc.Driver");
			String address = "123.57.22.224";
			String databaseName = "bsd";
			String name = "root";
			String password = "yang1290";

			conn = DriverManager.getConnection(
					"jdbc:mysql://" + address + "/" + databaseName + "?useUnicode=true&characterEncoding=utf-8", name,
					password);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/***
	 * 执行传入的sql语句，执行返回结果集合
	 * 
	 * @param sql
	 * @return 成功: ResultSet 失败: null
	 */
	public void getTasks(LinkedBlockingQueue<Task> tasksQueue, String sql) {
		/*
		 * sql =
		 * "SELECT id,product_type,region_range FROM t_business_production_plan_management WHERE production_status='待生产' AND production_begin_time<='"
		 * + now + "'";//查询当前时间之前的所有状态为带生产的数据。
		 */
		ResultSet resultsSet = null;
		try {
			Statement statement = conn.createStatement();
			resultsSet = statement.executeQuery(sql);
			if (resultsSet != null) {
				createTask(tasksQueue, resultsSet);
			}
			resultsSet.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("SQL语句异常：" + sql);
		}
		return;
	}

	private void createTask(LinkedBlockingQueue<Task> tasksQueue, ResultSet resultsSet) {
		try {
			while (resultsSet.next()) {
				System.out.println("resultSet: " + resultsSet.toString());
				int taskId = resultsSet.getInt("id");
				String production_plan_name = resultsSet.getString("production_plan_name");
				String product_type = resultsSet.getString("product_type");
				String region_code = resultsSet.getString("region_range");

				Task task = new Task(taskId, production_plan_name, product_type, region_code);// 创建一个Task

				System.out.println("task.toString-> " + task.toString());

				tasksQueue.add(task);// 把任务添加进去
				changeState(taskId, TaskState.TO_BE_DISTRIBUTED);// 更改状态
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return;
	}

	/***
	 * 改变状态为：status
	 * 
	 */
	private static void changeState(int id, String status) {
		try {
			Statement statement = conn.createStatement();
			String sql = "UPDATE t_business_production_plan_management SET production_status='" + status + "' WHERE id="
					+ id;
			if (statement.execute(sql)) {
				throw new SQLException();
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 从数据库中读取所有的数据信息
	 * 
	 * @param map
	 */

	public static boolean refreshMachinesInfo(Map<String, String> map) {

		int PORT = 8081;
		ResultSet resultSet = null;
		Statement statement = null;
		if (conn == null) {
			initConnection();
		}
		try {
			statement = conn.createStatement();
			String sql = "select i3properties.administrative_number, i7properties.machine_ip FROM (t_business_computing_resource_management LEFT JOIN i7properties ON t_business_computing_resource_management.resource_ip_address = i7properties.machine_ip) LEFT JOIN i3properties ON i7properties.i3_id = i3properties.id WHERE t_business_computing_resource_management.machine_status ='正常状态' AND i3properties.administrative_number <> '' AND i7properties.machine_ip <> ''";
			/*
			 * "select i3properties.administrative_number, "+
			 * "i7properties.machine_ip,i7properties.machine_port"+
			 * "FROM (t_business_computing_resource_management"+
			 * "LEFT JOIN i7properties"+
			 * "ON t_business_computing_resource_management.resource_ip_address = i7properties.machine_ip)"
			 * + "LEFT JOIN i3properties"+
			 * "ON i7properties.i3_id = i3properties.id"+
			 * "WHERE t_business_computing_resource_management.machine_status ='正常状态'"
			 */
			resultSet = statement.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (resultSet == null) {
			System.out.println("查询结果为：NULL");
		}
		try {
			while (resultSet.next()) {
				String ip = resultSet.getString("machine_ip");

				String logicPosition = resultSet.getString("administrative_number");
				String[] tmp = logicPosition.split("\\\\");
				String machinePosition = tmp[tmp.length - 1];
				map.put(machinePosition, ip + ":" + PORT);
			}
			statement.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 从数据库中读取所有的数据信息
	 * 
	 * @param map
	 */
	public static boolean refreshMachinesInfo(IdToMachine idToMachine) {

		int PORT = 8081;
		ResultSet resultSet = null;
		Statement statement = null;
		if (conn == null) {
			initConnection();
		}
		try {
			statement = conn.createStatement();
			String sql = "select i3properties.administrative_number, "
					+ "i7properties.machine_ip,i7properties.machine_port "
					+ "FROM (t_business_computing_resource_management " + "LEFT JOIN i7properties "
					+ "ON t_business_computing_resource_management.resource_ip_address = i7properties.machine_ip) "
					+ "LEFT JOIN i3properties " + "ON i7properties.i3_id = i3properties.id "
					+ "WHERE t_business_computing_resource_management.machine_status ='正常状态'"
					+ "AND i3properties.administrative_number <> '' " + "AND i7properties.machine_ip <> ''";
			resultSet = statement.executeQuery(sql);

		} catch (Exception e) {
			e.printStackTrace();
		}
		if (resultSet == null) {
			System.out.println("查询结果为：NULL");
		}
		try {
			while (resultSet.next()) {
				String ip = resultSet.getString("machine_ip");
				String logicPosition = resultSet.getString("administrative_number");
				String[] tmp = logicPosition.split("/");
				String machinePosition = tmp[tmp.length - 1];
				idToMachine.put(machinePosition, ip + ":" + PORT);
			}
			statement.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public static void setFinished(int taskId) {
		try {
			if (setTaskFinished == null) {
				setTaskFinished = new Update<>(FinishedTask.class);
				setTaskFinished.connect(getConnection());
			}

			setTaskFinished.execute(new FinishedTask(taskId));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void setDistributed(int taskId) {
		changeState(taskId, TaskState.UNDERWAY);
	}
	
	public static void setFailed(int taskId){
		changeState(taskId, TaskState.FAILED);
	}

	private static Update<FinishedTask> setTaskFinished = null;
}
